/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.just.dusin.ooii.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import org.dusin.webcoal.exception.CoalException;
import org.just.dusin.ooii.util.DateUtil;

/**
 *
 * @author Administrator
 */
public class QHJCXXBDao {

    private static Logger logger = Logger.getLogger(QHJCXXBDao.class.getName());
    private Connection conn = null;
    public int size;

    public QHJCXXBDao(Connection conn) {
        this.conn = conn;
    }

    //准备处理前n项记录，预设前n项记录YL1='1'
    public boolean prepareUpload(int n) throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "UPDATE QHJCXXB SET [uploadStatus]=1 from (select top " + n + " id from QHJCXXB where uploadStatus=0 order by XGRQ) as b where QHJCXXB.id = b.id ";
        try {
            pstmt = this.conn.prepareStatement(sql);
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    //完成传送，设置yl1=2
    public boolean finishUpload() throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "UPDATE QHJCXXB SET [uploadStatus]=2 where uploadStatus=1 ";
        try {
            pstmt = this.conn.prepareStatement(sql);
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    public List<CLCSBean> findAll(String orgCode, int yearFrom, int monthFrom, int yearTo, int monthTo,int beginIndex, int size) throws CoalException {
        List<CLCSBean> all = new ArrayList<CLCSBean>();
        PreparedStatement pstmt = null;
        PreparedStatement pstmtsize = null;
        String sqlsize = "select count(*) from QHJCXXB where orgcode like '" + orgCode + "%' and XGRQ>=? and XGRQ<= ?";

        String sql = "select  id,[SHI],[XIAN],[MKMC],[NHDNL],[NHDNLDW],[YHDNL],[YHDNLDW],[RHDNL],[RHDNLDW],[SHDNL],[SHDNLDW],[ZSXS],[SBMC],[AQZSH],[NJZSH] ,XGRQ,orgcode FROM (select  id,orgcode,[SHI],[XIAN],[MKMC],[NHDNL],[NHDNLDW],[YHDNL],[YHDNLDW],[RHDNL],[RHDNLDW],[SHDNL],[SHDNLDW],[ZSXS],[SBMC],[AQZSH],[NJZSH] ,XGRQ,row_number() over(order by id) rn from QHJCXXB  where QHJCXXB.orgcode like '" + orgCode + "%'  and XGRQ>=? and XGRQ<=?) t where rn >=? and rn<?";

        try {
            pstmtsize = this.conn.prepareStatement(sqlsize);

            String startTime = "" + yearFrom + "-" + monthFrom + "-" + 1+" 00:00:00";
            
            Calendar cal = Calendar.getInstance();
            cal.set(yearTo, monthTo, 1,23,59,59);
            cal.add(Calendar.DATE, -1);
            String stopTime = "" + cal.get(Calendar.YEAR) + "-" + (cal.get(Calendar.MONTH)+1) + "-" + cal.get(Calendar.DATE) + " 23:59:59";
            
            pstmtsize.setString(1, startTime);
            pstmtsize.setString(2, stopTime);

            ResultSet rssize = pstmtsize.executeQuery(); //
            rssize.next();
            this.size = rssize.getInt(1);

            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, startTime);
            pstmt.setString(2, stopTime);
            pstmt.setInt(3, beginIndex + 1);
            pstmt.setInt(4, beginIndex + size + 1);
            ResultSet rs = pstmt.executeQuery(); //
             long id;

            String SHI;
            String XIAN;
            String MKMC;
            Date XGRQ;
            double yearPlan;
            String yearPlanUnit;
            double monthPlan;
            String monthPlanUnit;
            double dayPlan;
            String dayPlanUnit;
            double hourPlan;
            String hourPlanUnit;
            double rate;
            String deviceName;
            String securityNo;
            String yearCheckNo;

            CLCSBean clcs = null;
            while (rs.next()) { //
                clcs = new CLCSBean();
                id = rs.getLong(1);
                SHI = rs.getString(2);
                XIAN = rs.getString(3);
                MKMC = rs.getString(4);
                yearPlan = rs.getDouble(5);
                yearPlanUnit = rs.getString(6);
                monthPlan = rs.getDouble(7);
                monthPlanUnit = rs.getString(8);
                dayPlan = rs.getDouble(9);
                dayPlanUnit = rs.getString(10);
                hourPlan = rs.getDouble(11);
                hourPlanUnit = rs.getString(12);
                rate = rs.getByte(13);
                deviceName = rs.getString(14);
                securityNo = rs.getString(15);
                yearCheckNo = rs.getString(16);
                XGRQ = rs.getTimestamp(17);
                
                clcs.setId(id);
                clcs.setCityName(SHI);
                clcs.setCountyName(XIAN);
                clcs.setMineName(MKMC);
                if ("KT".equals(yearPlanUnit.toUpperCase())) {
                    clcs.setYearPlan(yearPlan * 1000);
                } else {
                    clcs.setYearPlan(yearPlan);
                }
                if ("KT".equals(monthPlanUnit.toUpperCase())) {
                    clcs.setMonthPlan(monthPlan * 1000);
                } else {
                    clcs.setMonthPlan(monthPlan);
                }
                if ("KT".equals(dayPlanUnit.toUpperCase())) {
                    clcs.setDayPlan(dayPlan * 1000);
                } else {
                    clcs.setDayPlan(dayPlan);
                }
                if ("KT".equals(hourPlanUnit.toUpperCase())) {
                    clcs.setHourPlan(hourPlan * 1000);
                } else {
                    clcs.setHourPlan(hourPlan);
                }

                clcs.setRate(rate);

                clcs.setSecurityNo(securityNo);
                clcs.setYearlyCheckNo(yearCheckNo);
                clcs.setXgrq(XGRQ);
                int year = XGRQ.getYear()+1900;
                int month = XGRQ.getMonth() + 1;
                int day = XGRQ.getDate();
                int hour = XGRQ.getHours();
                int min = XGRQ.getMinutes();
                clcs.setYear((short) year);
                clcs.setMonth((byte) month);
                clcs.setDay((byte) day);
                clcs.setHour((byte) hour);
                clcs.setMin((byte) min);
                clcs.setMineCode(rs.getString(18));
                all.add(clcs); //
//System.out.println(all.size());
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new CoalException(e);
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw new CoalException(e);
            }
        }
        return all;
    }

    //获取前几项未传送信息
    public List<CLCSBean> getTops(int n) throws Exception {
        List<CLCSBean> all = new ArrayList<CLCSBean>();
        PreparedStatement pstmt = null;
        String sql = "select top " + n + " id,[SHI],[XIAN],[MKMC],[NHDNL],[NHDNLDW],[YHDNL],[YHDNLDW],[RHDNL],[RHDNLDW],[SHDNL],[SHDNLDW],[ZSXS],[SBMC],[AQZSH],[NJZSH] ,XGRQ from QHJCXXB  where uploadStatus=1 order by XGRQ asc;";
        try {
            pstmt = this.conn.prepareStatement(sql);
            //pstmt.setInt(1, n);            
            ResultSet rs = pstmt.executeQuery(); //
            long id;

            String SHI;
            String XIAN;
            String MKMC;
            Date XGRQ;
            double yearPlan;
            String yearPlanUnit;
            double monthPlan;
            String monthPlanUnit;
            double dayPlan;
            String dayPlanUnit;
            double hourPlan;
            String hourPlanUnit;
            double rate;
            String deviceName;
            String securityNo;
            String yearCheckNo;

            while (rs.next()) {
                id = rs.getLong(1);
                SHI = rs.getString(2);
                XIAN = rs.getString(3);
                MKMC = rs.getString(4);
                yearPlan = rs.getDouble(5);
                yearPlanUnit = rs.getString(6);
                monthPlan = rs.getDouble(7);
                monthPlanUnit = rs.getString(8);
                dayPlan = rs.getDouble(9);
                dayPlanUnit = rs.getString(10);
                hourPlan = rs.getDouble(11);
                hourPlanUnit = rs.getString(12);
                rate = rs.getByte(13);
                deviceName = rs.getString(14);
                securityNo = rs.getString(15);
                yearCheckNo = rs.getString(16);
                XGRQ = rs.getTimestamp(17);
                CLCSBean clcs = new CLCSBean();
                clcs.setId(id);
                clcs.setCityName(SHI);
                clcs.setCountyName(XIAN);
                clcs.setMineName(MKMC);
                if ("KT".equals(yearPlanUnit.toUpperCase())) {
                    clcs.setYearPlan(yearPlan * 1000);
                } else {
                    clcs.setYearPlan(yearPlan);
                }
                if ("KT".equals(monthPlanUnit.toUpperCase())) {
                    clcs.setMonthPlan(monthPlan * 1000);
                } else {
                    clcs.setMonthPlan(monthPlan);
                }
                if ("KT".equals(dayPlanUnit.toUpperCase())) {
                    clcs.setDayPlan(dayPlan * 1000);
                } else {
                    clcs.setDayPlan(dayPlan);
                }
                if ("KT".equals(hourPlanUnit.toUpperCase())) {
                    clcs.setHourPlan(hourPlan * 1000);
                } else {
                    clcs.setHourPlan(hourPlan);
                }

                clcs.setRate(rate);

                clcs.setSecurityNo(securityNo);
                clcs.setYearlyCheckNo(yearCheckNo);
                clcs.setXgrq(XGRQ);
                int year = XGRQ.getYear()+1900;
                int month = XGRQ.getMonth() + 1;
                int day = XGRQ.getDate();
                int hour = XGRQ.getHours();
                int min = XGRQ.getMinutes();
                clcs.setYear((short) year);
                clcs.setMonth((byte) month);
                clcs.setDay((byte) day);
                clcs.setHour((byte) hour);
                clcs.setMin((byte) min);
                all.add(clcs);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;

        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return all;
    }

    public boolean changeStatusById(long id, int status) throws Exception {
        boolean flag = false;

        PreparedStatement pstmt = null;
        String sql = "UPDATE QHJCXXB SET [uploadStatus]=? WHERE id=?";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, new Integer(status).toString());
            pstmt.setLong(2, id);
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    public int getTotalSize() {
        return size;
    }

    public boolean doCreate(CLCSBean clcs) throws Exception{
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "INSERT INTO [QHJCXXB](orgcode,[SHI],[XIAN],[MKMC],[NHDNL],[NHDNLDW],[YHDNL],[YHDNLDW],[RHDNL],[RHDNLDW],[SHDNL],[SHDNLDW],[ZSXS],[SBMC],[AQZSH],[NJZSH] ,XGRQ)"
                + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            pstmt = this.conn.prepareStatement(sql);
            
            pstmt.setString(1, clcs.getMineCode());
            pstmt.setString(2, clcs.getCityName());
            pstmt.setString(3, clcs.getCountyName());
            pstmt.setString(4, clcs.getMineName());
            pstmt.setDouble(5, clcs.getYearPlan());
            pstmt.setString(6, "t");
            pstmt.setDouble(7, clcs.getMonthPlan());
            pstmt.setString(8, "t");
            pstmt.setDouble(9, clcs.getDayPlan());
            pstmt.setString(10, "t");
            pstmt.setDouble(11, clcs.getHourPlan());
            pstmt.setString(12, "t");
            pstmt.setDouble(13, clcs.getRate());
            pstmt.setString(14, clcs.getDeviceName());
            pstmt.setString(15, clcs.getSecurityNo());
            pstmt.setString(16, clcs.getYearlyCheckNo());
            String time = clcs.getYear()+"-"+clcs.getMonth()+"-"+clcs.getDay()+" "+clcs.getMin()+":"+clcs.getHour()+":00";
            pstmt.setString(17,  time);

            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }
    
    public Date getLatestTime(String orgCode) throws Exception {
        Date rtDate = null;
        PreparedStatement pstmt = null;
        String sql = "select top 1 XGRQ from  QHJCXXB  WHERE orgCode=? order by XGRQ desc ";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, orgCode);
          
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                Date updatetime = rs.getTimestamp(1);      
                rtDate = updatetime;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return rtDate;
    }
}
